package group.teachingmanagerbk.mapper;

import group.teachingmanagerbk.vo.member.Department;
import group.teachingmanagerbk.vo.member.Student;
import group.teachingmanagerbk.vo.member.Teacher;
import group.teachingmanagerbk.vo.member.Tester;
import org.apache.ibatis.annotations.*;

import java.util.ArrayList;

@Mapper
public interface MemberMapper {

   /* *//* 查询所有学院的信息 *//*
    @Select("select department_id as departmentId, name as departmentName from department")
    ArrayList<Department> getDepartmentInfo();*/

    /* 根据条件统计教师数量 */
    @Select("select count(*) from teacher " +
            "where teacher_number like concat('%',#{teacherNumber},'%') " +
            "and teacher.name like concat('%',#{name},'%') " +
            " ")
    int getTeacherCount(Teacher param);

    /* 根据条件查询教师信息（分页） */
    @Select("select teacher.*" +
            "from teacher " +
            "where teacher.teacher_number like concat('%',#{param.teacherNumber},'%') " +
            "and teacher.name like concat('%',#{param.name},'%') " +

            "limit #{index}, #{size}")
    ArrayList<Teacher> getTeacherInfo(Teacher param, Integer index, Integer size);

    @Select("select * from teacher "
            )
    ArrayList<Teacher> getTeachers();

    /* 插入一条教师信息 */
    @Insert("insert into teacher (teacher_number, name, title) values (#{teacherNumber}, #{name}, #{title})")
    void insertTeacherInfo(Teacher teacher);

    /* 根据教师id删除相应的记录 */
    void deleteTeachersInfo(String[] teacherIds);

    /* 根据教师id查询相应的记录 */
    @Select("select t.*from teacher t " +
            "where t.teacher_id = #{teacherId}")
    Teacher getTeacherInfoById(String teacherId);

    /* 根据教师id修改教师的信息 */
    @Update("update teacher set teacher_number = #{teacherNumber}, " +
            "name = #{name}, title = #{title} " +
            "where teacher_id = #{teacherId}")
    void updateTeacherInfoById(Teacher teacher);

    /* 根据教师number查询相应的记录 */
    @Select("select * from teacher where teacher_number = #{teacherNumber}")
    Teacher getTeacherInfoByNumber(String number);

    /* 根据教师number修改教师的信息 */
    @Update("update teacher set name = #{name}, " +
            "title = #{title}" +
            "where teacher_number = #{teacherNumber}")
    void updateTeacherInfoByNumber(Teacher teacher);

    /* 根据条件统计学生数量 */
    @Select("select count(*) from student where student_number " +
            "like concat('%',#{studentNumber},'%') " +
            "and name like concat('%',#{name},'%') " +
            "and student_class like concat('%',#{studentClass},'%')")
    int getStudentCount(Student param);

    /* 根据条件查询学生信息（分页） */
    @Select("select * from student where student_number " +
            "like concat('%',#{param.studentNumber},'%') " +
            "and name like concat('%',#{param.name},'%') " +
            "and student_class like concat('%',#{param.studentClass},'%') " +
            "limit #{index}, #{size}")
    ArrayList<Student> getStudentInfo(Student param, Integer index, Integer size);

    /* 插入一条学生信息 */
    @Insert("insert into student (student_number, name, student_class, profession) values (#{studentNumber}, #{name}, #{studentClass}, #{profession})")
    void insertStudentInfo(Student student);

    /* 根据学生id删除相应的记录 */
    void deleteStudentsInfo(String[] studentIds);

    /* 根据学生id查询相应的记录 */
    @Select("select * from student where student_id = #{studentId}")
    Student getStudentInfoById(String studentId);

    /* 根据学生name查询相应的记录 */
    @Select("select * from student where name = #{name}")
    Student getStudentInfoByName(String name);

    /* 根据学生number查询相应的记录 */
    @Select("select * from student where student_number = #{studentNumber}")
    Student getStudentInfoByNumber(String number);

    /* 根据学生id修改学生的信息 */
    @Update("update student set student_number = #{studentNumber}, " +
            "name = #{name}, student_class = #{studentClass}, profession = #{profession} " +
            "where student_id = #{studentId}")
    void updateStudentInfoById(Student student);

    /* 根据学生name修改学生的信息 */
    @Update("update student set student_number = #{studentNumber}, " +
            "student_class = #{studentClass}, profession = #{profession} " +
            "where name = #{name}")
    void updateStudentInfoByName(Student student);

    /* 根据学生number修改学生的信息 */
    @Update("update student set name = #{name}, " +
            "student_class = #{studentClass}, profession = #{profession}" +
            "where student_number = #{studentNumber}")
    void updateStudentInfoByNumber(Student student);


    /* 根据条件统计Tester数量 */
    @Select("select count(*) from tester " +
            "where tester_number like concat('%',#{testerNumber},'%') " +
            "and tester.name like concat('%',#{name},'%') " +
            " ")
    int getTesterCount(Tester param);

    /* 根据条件查询Tester信息（分页） */
    @Select("select tester.*" +
            "from tester " +
            "where tester.tester_number like concat('%',#{param.testerNumber},'%') " +
            "and tester.name like concat('%',#{param.name},'%') " +

            "limit #{index}, #{size}")
    ArrayList<Tester> getTesterInfo(Tester param, Integer index, Integer size);

    /* 插入一条Tester信息 */
    @Insert("insert into tester (tester_number, name, title) values (#{testerNumber}, #{name}, #{title})")
    void insertTesterInfo(Tester tester);

    /* 根据Tester删除相应的记录 */
    void deleteTestersInfo(String[] testerIds);

    /* 根据Tester查询相应的记录 */
    @Select("select t.*from tester t " +
            "where t.tester_id = #{testerId}")
    Tester getTesterInfoById(String testerId);

    /* 根据Tester修改Tester的信息 */
    @Update("update tester set tester_number = #{testerNumber}, " +
            "name = #{name}, title = #{title} " +
            "where tester_id = #{testerId}")
    void updateTesterInfoById(Tester tester);

    /* 根据Tester number查询相应的记录 */
    @Select("select * from tester where tester_number = #{testerNumber}")
    Tester getTesterInfoByNumber(String number);

    /* 根据Tester number修改学生的信息 */
    @Update("update tester set name = #{name}, " +
            "title = #{title}" +
            "where tester_number = #{testerNumber}")
    void updateTesterInfoByNumber(Tester tester);

}
